﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQL.SQLite
{
    /// <summary>
    /// SQLite使用类(以后会使用脚本)
    /// </summary>
    public class SQLiteHelper
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        SQLiteConnection sql;
        int hour;
        /// <summary>
        /// 统计的数据
        /// </summary>
        public Statistics 统计数据;
        SQLiteCommand update_cmd;

        public SQLiteHelper()
        {
            Init();
            TestAndCreateTable();
            Refresh();
        }

        /// <summary>
        /// 初始化数据库连接和打开连接
        /// </summary>
        public void Init()
        {
            统计数据 = new Statistics();
            sql = new SQLiteConnection
            {
                ConnectionString = Properties.Settings.Default.数据库连接字符串
            };
            sql.Open();
        }

        /// <summary>
        /// 测试表是否存在,不存在则创建
        /// </summary>
        public void TestAndCreateTable()
        {
            var sqlcmd = sql.CreateCommand();
            sqlcmd.CommandText = "select * from yields_data limit 1";
            try
            {
                sqlcmd.ExecuteScalar();
            }
            catch (SQLiteException e)
            {
                Console.WriteLine(e.ToString());
                var script_path = Properties.Settings.Default.SQLite创建表脚本;
                var script = File.ReadAllText(script_path);
                sqlcmd.CommandText = script;
                sqlcmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (sql != null)
                sql.Close();
        }

        /// <summary>
        /// 从数据库刷新数据
        /// </summary>
        public void Refresh()
        {
            if (sql.State == System.Data.ConnectionState.Open)
            {
                var now = DateTime.Now;
                if (hour != now.Hour)
                {
                    var sqlcmd = sql.CreateCommand();
                    sqlcmd.CommandText = "select total,ok,ng,other from yields_data where start_date = @start_date limit 1";
                    sqlcmd.Parameters.Add(new SQLiteParameter("@start_date", now.ToString("yyyy-MM-dd HH:00:00.000")));//yyyy-MM-dd HH:mm:ss.fff
                    var reader = sqlcmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        统计数据.Total = reader.GetInt32(0);
                        统计数据.Ok = reader.GetInt32(1);
                        统计数据.Ng = reader.GetInt32(2);
                        统计数据.Other = reader.GetInt32(3);
                    }
                    else
                    {
                        统计数据.Init();
                        var insert_cmd = sql.CreateCommand();
                        insert_cmd.CommandText = @"insert into `main`.`yields_data` (`start_date`,`total`,`ok`,`ng`,`other`) " +
                            "VALUES (@start_date,@total,@ok,@ng,@other)";
                        insert_cmd.Parameters.Add(new SQLiteParameter("@start_date", now.ToString("yyyy-MM-dd HH:00:00.000")));//yyyy-MM-dd HH:mm:ss.fff
                        insert_cmd.Parameters.Add(new SQLiteParameter("@total", 统计数据.Total));
                        insert_cmd.Parameters.Add(new SQLiteParameter("@ok", 统计数据.Ok));
                        insert_cmd.Parameters.Add(new SQLiteParameter("@ng", 统计数据.Ng));
                        insert_cmd.Parameters.Add(new SQLiteParameter("@other", 统计数据.Other));
                        insert_cmd.ExecuteNonQuery();
                    }
                    hour = now.Hour;
                }
            }
        }

        /// <summary>
        /// 更新数据到数据库, 需要注意的是SQLite数据库写入效率较低,避免频繁调用
        /// </summary>
        public void Update()
        {
            if (sql.State == System.Data.ConnectionState.Open)
            {
                var now = DateTime.Now;
                if (update_cmd == null)
                    update_cmd = sql.CreateCommand();

                update_cmd.CommandText = @"update `main`.`yields_data` set `total` = @total,`ok` = @ok,`ng` = @ng,`other` = @other " +
                    "where `start_date` = @start_date";
                update_cmd.Parameters.Add(new SQLiteParameter("@start_date", now.ToString("yyyy-MM-dd HH:00:00.000")));//yyyy-MM-dd HH:mm:ss.fff
                update_cmd.Parameters.Add(new SQLiteParameter("@total", 统计数据.Total));
                update_cmd.Parameters.Add(new SQLiteParameter("@ok", 统计数据.Ok));
                update_cmd.Parameters.Add(new SQLiteParameter("@ng", 统计数据.Ng));
                update_cmd.Parameters.Add(new SQLiteParameter("@other", 统计数据.Other));
                /*
                update_cmd.CommandText = string.Format(@"update `main`.`yields_data` set `total` ='{0}',`ok` = '{1}',`ng` = '{2}',`other` = '{3}' " +
                    "where `start_date` = '{4}'", 统计数据.Total, 统计数据.Ok, 统计数据.Ng, 统计数据.Other, now.ToString("yyyy-MM-dd HH:00:00.000"));
                    */
                update_cmd.ExecuteNonQuery();
            }
        }
    }
}
